﻿-- =============================================
-- Author:		Taylor Chase
-- Create date: March 11, 2016
-- Description:	Sync Student Database with RafikiDB
-- =============================================
CREATE PROCEDURE [dbo].[SyncDataFromStudentDBtoRafiki] 

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @JSONtoWrite TABLE (JSONvalue nvarchar(MAX),EntityID int,JSONvalue2 nvarchar(MAX))
	DECLARE @ExecResult TABLE (RequestorEntityID int,DataTableType nvarchar(100),DataID int,JSONdata nvarchar(max))
	DECLARE @CheckRequestorEntityID int,@CheckDataTableType nvarchar(100),@CheckDataID int,@CheckJSONdata nvarchar(max)
	DECLARE @JSONvalue nvarchar(MAX), @ToUpdateEntityID int, @JSONvalue2 nvarchar(MAX), @CreatedID int, @WriteType nvarchar(100)
	DECLARE @OpenQueryText nvarchar(MAX)
	
	DECLARE @WriteCursor CURSOR
	
	-- Student Insert/Update Info Section - NOW FUNCTION
	BEGIN
	
	PRINT 'Writing Student Info'
	
	SET @WriteCursor = CURSOR FAST_FORWARD
	FOR 
		SELECT JSONvalue,EntityID,JSONvalue2 FROM dbo.DataConversion_StudentInfo()
		
	OPEN @WriteCursor
	FETCH NEXT FROM @WriteCursor
	INTO @JSONvalue,@ToUpdateEntityID,@JSONvalue2
	WHILE @@FETCH_STATUS = 0
	BEGIN
		-- If there is an admissions request, insert/update that first.
		-- AND if the student is being created, append the relationship creation logic to the student record.
		IF NOT @JSONvalue2 IS NULL BEGIN
			SET @CreatedID = NULL
			-- Retrieve the EntityID of the admissions request. (If being created it will be -1)
			SELECT @CreatedID = CAST(StringValue AS int) 
			FROM dbo.parseJSON(@JSONvalue2) WHERE [NAME] = 'EntityID'
			IF @ToUpdateEntityID IS NULL 
				PRINT 'Error when finding EntityID in string: ' + COALESCE(@JSONvalue2,'|')
			ELSE BEGIN
				DELETE FROM @ExecResult
				-- Insert or update the Admissions Request.
				INSERT INTO @ExecResult(RequestorEntityID,DataTableType,DataID,JSONdata)
				EXEC MBOA.RafikiDB.dbo.WriteDataToDB 0,'Entity',@JSONvalue2,@CreatedID

				SELECT @CheckJSONdata = JSONdata
				FROM @ExecResult
				-- There is an error - print it
				IF CHARINDEX('"DataError"',@CheckJSONdata) <> 0
					PRINT @CheckJSONdata
				ELSE BEGIN
					PRINT 'Good - ' + @CheckJSONdata
					SET @CreatedID = NULL
					-- Retrieve the EntityID from the result of the Insert Or Update statement.
					SELECT @CreatedID = CAST(StringValue AS int) 
					FROM dbo.parseJSON(@CheckJSONdata) WHERE [NAME] = 'EntityID'
					IF @CreatedID IS NULL 
						PRINT 'Error when finding EntityID in string: ' + COALESCE(@JSONvalue2,'|')
					ELSE BEGIN
						-- @CreatedID is the ID of the newly created admissions object.
						-- If the Student Entity exists (@ToUpdateEntityID <> -1) then create the APPLIED relationship
						IF @ToUpdateEntityID <> -1 BEGIN
							DELETE FROM @ExecResult
							-- Insert or update the APPLIED relationship.
							SET @OpenQueryText = '{"ShortRelationshipName":"APPLIED",
													   "Entity1ID":"' + CAST(@ToUpdateEntityID AS NVARCHAR) + '",
													   "Entity2ID":"' + CAST(@CreatedID AS NVARCHAR) + '"}'

							INSERT INTO @ExecResult(RequestorEntityID,DataTableType,DataID,JSONdata)
							EXEC MBOA.RafikiDB.dbo.WriteDataToDB 0,'EntityRelationship',@OpenQueryText,@CreatedID

							SELECT @CheckJSONdata = JSONdata
							FROM @ExecResult
							-- There is an error - print it
							IF CHARINDEX('"DataError"',@CheckJSONdata) <> 0
								PRINT @CheckJSONdata
							PRINT 'Good REL - ' + @CheckJSONdata
						END ELSE BEGIN
						-- If the Student Entity does not exist, append the Applied relationship to the 
						-- student JSON.  This will create the student and also create the APPLIED relationship
						-- so the student entity creation will not fail.
						-- @CreatedID is the EntityID of the Admissions object, which is necessary if
						-- the Student Entity is being created.
							-- First step is remove trailing }
							SET @JSONvalue = SUBSTRING(@JSONvalue,1,LEN(@JSONvalue)-1)
							-- Next, append Entity2ID and the APPLIED relationship name to the student record.
							SET @JSONvalue = @JSONvalue + ',"Entity2ID":"' + CAST(@CreatedID AS NVARCHAR) + '","ShortRelationshipName":"' + 'APPLIED' + '"}'
						END
					END
				END
			END
		END
	
		DELETE FROM @ExecResult
		-- Makes sure that names or notes with ' in their name will still work.
		INSERT INTO @ExecResult(RequestorEntityID,DataTableType,DataID,JSONdata)
		EXEC MBOA.RafikiDB.dbo.WriteDataToDB 0,'Entity',@JSONvalue,@ToUpdateEntityID

		SELECT @CheckJSONdata = JSONdata
		FROM @ExecResult
		-- There is an error - print it
		IF CHARINDEX('"DataError"',@CheckJSONdata) <> 0
			PRINT @CheckJSONdata
		ELSE
			PRINT CAST(@ToUpdateEntityID AS NVARCHAR) + ' - GOOD ' + @CheckJSONdata
			
		FETCH NEXT FROM @WriteCursor
		INTO @JSONvalue,@ToUpdateEntityID,@JSONvalue2
	END
	CLOSE @WriteCursor
	DEALLOCATE @WriteCursor

	DELETE FROM @ExecResult
	DELETE FROM @JSONtoWrite

	--PRINT 'Finished writing student info.'
	----************************************************************************************
	END
	
	---- Parent/Staff Insert/Update Info Section - NOW FUNCTION
	BEGIN
	
	PRINT 'INSERT/UPDATE Parent and Staff Info'
	SET @WriteCursor = CURSOR FAST_FORWARD
	FOR 
		SELECT JSONvalue,EntityID,JSONvalue2 FROM dbo.[DataConversion_ParentInfo]()
		
	OPEN @WriteCursor
	FETCH NEXT FROM @WriteCursor
	INTO @JSONvalue,@ToUpdateEntityID,@JSONvalue2
	WHILE @@FETCH_STATUS = 0
	BEGIN
		-- Write the Parent info:
		DELETE FROM @ExecResult

		INSERT INTO @ExecResult(RequestorEntityID,DataTableType,DataID,JSONdata)
		EXEC MBOA.RafikiDB.dbo.WriteDataToDB 0,'Entity',@JSONvalue,@ToUpdateEntityID

		SELECT @CheckJSONdata = JSONdata
		FROM @ExecResult
		-- There is an error - print it
		IF CHARINDEX('"DataError"',@CheckJSONdata) <> 0
			PRINT @CheckJSONdata
		ELSE
			PRINT CAST(@ToUpdateEntityID AS NVARCHAR) + ' - GOOD ' + @CheckJSONdata
			
		FETCH NEXT FROM @WriteCursor
		INTO @JSONvalue,@ToUpdateEntityID,@JSONvalue2
	END
	CLOSE @WriteCursor
	DEALLOCATE @WriteCursor

	DELETE FROM @ExecResult
	DELETE FROM @JSONtoWrite
	
	END
	
	---- Parent/Staff relationships Section - NOW FUNCTION
	BEGIN
	
	PRINT 'Parent/Staff Relationship Update'
	SET @WriteCursor = CURSOR FAST_FORWARD
	FOR 
		SELECT JSONvalue,EntityID,JSONvalue2 FROM dbo.DataConversion_ParentStaffRelationships()
		
	OPEN @WriteCursor
	FETCH NEXT FROM @WriteCursor
	INTO @JSONvalue,@ToUpdateEntityID,@JSONvalue2
	WHILE @@FETCH_STATUS = 0
	BEGIN
		-- Write the Parent info:
		DELETE FROM @ExecResult

		INSERT INTO @ExecResult(RequestorEntityID,DataTableType,DataID,JSONdata)
		EXEC MBOA.RafikiDB.dbo.WriteDataToDB 0,'EntityRelationship',@JSONvalue,@ToUpdateEntityID

		SELECT @CheckJSONdata = JSONdata
		FROM @ExecResult
		-- There is an error - print it
		IF CHARINDEX('"DataError"',@CheckJSONdata) <> 0
			PRINT 'Error: ' + @CheckJSONdata
		ELSE
			PRINT CAST(@ToUpdateEntityID AS NVARCHAR) + ' - GOOD ' + @CheckJSONdata
			
		FETCH NEXT FROM @WriteCursor
		INTO @JSONvalue,@ToUpdateEntityID,@JSONvalue2
	END
	CLOSE @WriteCursor
	DEALLOCATE @WriteCursor

	DELETE FROM @ExecResult
	DELETE FROM @JSONtoWrite	
	END

	---- All Person Self Relationships Section - NOW FUNCTION
	BEGIN
	
	PRINT 'Update SELF relationships'
	SET @WriteCursor = CURSOR FAST_FORWARD
	FOR 
		SELECT JSONvalue,EntityID,JSONvalue2 FROM dbo.DataConversion_SelfRelationshipCheck()
		
	OPEN @WriteCursor
	FETCH NEXT FROM @WriteCursor
	INTO @JSONvalue,@ToUpdateEntityID,@JSONvalue2
	WHILE @@FETCH_STATUS = 0
	BEGIN
		-- Write the Parent info:
		DELETE FROM @ExecResult

		INSERT INTO @ExecResult(RequestorEntityID,DataTableType,DataID,JSONdata)
		EXEC MBOA.RafikiDB.dbo.WriteDataToDB 0,'EntityRelationship',@JSONvalue,@ToUpdateEntityID

		SELECT @CheckJSONdata = JSONdata
		FROM @ExecResult
		-- There is an error - print it
		IF CHARINDEX('"DataError"',@CheckJSONdata) <> 0
			PRINT 'Error: ' + @CheckJSONdata
		ELSE
			PRINT CAST(@ToUpdateEntityID AS NVARCHAR) + ' - GOOD ' + @CheckJSONdata
			
		FETCH NEXT FROM @WriteCursor
		INTO @JSONvalue,@ToUpdateEntityID,@JSONvalue2
	END
	CLOSE @WriteCursor
	DEALLOCATE @WriteCursor

	DELETE FROM @ExecResult
	DELETE FROM @JSONtoWrite	
	END

	---- Student/Staff/Parent Email Contacts Section - NOW FUNCTION
	BEGIN
	
	PRINT 'Parent/Staff/Student Email Contacts'
	SET @WriteCursor = CURSOR FAST_FORWARD
	FOR 
		SELECT JSONvalue,EntityID,JSONvalue2,WriteType FROM DBO.DataConversion_EmailContacts()
		
	OPEN @WriteCursor
	FETCH NEXT FROM @WriteCursor
	INTO @JSONvalue,@ToUpdateEntityID,@JSONvalue2,@WriteType
	WHILE @@FETCH_STATUS = 0
	BEGIN
		-- Write the Parent info:
		DELETE FROM @ExecResult

		INSERT INTO @ExecResult(RequestorEntityID,DataTableType,DataID,JSONdata)
		EXEC MBOA.RafikiDB.dbo.WriteDataToDB 0,@WriteType,@JSONvalue,@ToUpdateEntityID

		SELECT @CheckJSONdata = JSONdata
		FROM @ExecResult
		-- There is an error - print it
		IF CHARINDEX('"DataError"',@CheckJSONdata) <> 0
			PRINT @CheckJSONdata
		ELSE
			PRINT CAST(@ToUpdateEntityID AS NVARCHAR) + ' - GOOD ' + @CheckJSONdata
			
		FETCH NEXT FROM @WriteCursor
		INTO @JSONvalue,@ToUpdateEntityID,@JSONvalue2,@WriteType
	END
	CLOSE @WriteCursor
	DEALLOCATE @WriteCursor

	DELETE FROM @ExecResult
	DELETE FROM @JSONtoWrite	

		
		
	END
	
	-- Application Info / Enrollment Section - NOW FUNCTION
	BEGIN
	
	PRINT 'Update Application/Enrollment Info'
	SET @WriteCursor = CURSOR FAST_FORWARD
	FOR 
		SELECT JSONvalue,EntityID,JSONvalue2,WriteType FROM DBO.[DataConversion_ApplicationsEnrollments]()
		
	OPEN @WriteCursor
	FETCH NEXT FROM @WriteCursor
	INTO @JSONvalue,@ToUpdateEntityID,@JSONvalue2,@WriteType
	WHILE @@FETCH_STATUS = 0
	BEGIN
		-- Write the Parent info:
		DELETE FROM @ExecResult

		INSERT INTO @ExecResult(RequestorEntityID,DataTableType,DataID,JSONdata)
		EXEC MBOA.RafikiDB.dbo.WriteDataToDB 0,@WriteType,@JSONvalue,@ToUpdateEntityID

		SELECT @CheckJSONdata = JSONdata
		FROM @ExecResult
		-- There is an error - print it
		IF CHARINDEX('"DataError"',@CheckJSONdata) <> 0
			PRINT @CheckJSONdata
		ELSE
			PRINT CAST(@ToUpdateEntityID AS NVARCHAR) + ' - GOOD ' + @CheckJSONdata
			
		FETCH NEXT FROM @WriteCursor
		INTO @JSONvalue,@ToUpdateEntityID,@JSONvalue2,@WriteType
	END
	CLOSE @WriteCursor
	DEALLOCATE @WriteCursor

	DELETE FROM @ExecResult
	DELETE FROM @JSONtoWrite	

	
	END
	
	-- Application Process Steps Section - NOW FUNCTION
	BEGIN
	
	PRINT 'Admissions Process Steps'
	SET @WriteCursor = CURSOR FAST_FORWARD
	FOR 
		SELECT JSONvalue,EntityID,JSONvalue2,WriteType FROM DBO.[DataConversion_AdmissionsProcess]()
		
	OPEN @WriteCursor
	FETCH NEXT FROM @WriteCursor
	INTO @JSONvalue,@ToUpdateEntityID,@JSONvalue2,@WriteType
	WHILE @@FETCH_STATUS = 0
	BEGIN
		-- Write the Parent info:
		DELETE FROM @ExecResult

		INSERT INTO @ExecResult(RequestorEntityID,DataTableType,DataID,JSONdata)
		EXEC MBOA.RafikiDB.dbo.WriteDataToDB 0,@WriteType,@JSONvalue,@ToUpdateEntityID

		SELECT @CheckJSONdata = JSONdata
		FROM @ExecResult
		-- There is an error - print it
		IF CHARINDEX('"DataError"',@CheckJSONdata) <> 0
			PRINT @CheckJSONdata
		ELSE
			PRINT CAST(@ToUpdateEntityID AS NVARCHAR) + ' - GOOD ' + @CheckJSONdata
			
		FETCH NEXT FROM @WriteCursor
		INTO @JSONvalue,@ToUpdateEntityID,@JSONvalue2,@WriteType
	END
	CLOSE @WriteCursor
	DEALLOCATE @WriteCursor

	DELETE FROM @ExecResult
	DELETE FROM @JSONtoWrite
		
	END
	
	-- Dorm Parent / Dorm Kid / Station Kid - NOW FUNCTION
	BEGIN
	
	PRINT 'Dorm Parent / Dorm Kid / Station Kid'
	SET @WriteCursor = CURSOR FAST_FORWARD
	FOR 
		SELECT JSONvalue,EntityID,JSONvalue2,WriteType FROM DBO.[DataConversion_Dorms]()
		
	OPEN @WriteCursor
	FETCH NEXT FROM @WriteCursor
	INTO @JSONvalue,@ToUpdateEntityID,@JSONvalue2,@WriteType
	WHILE @@FETCH_STATUS = 0
	BEGIN
		-- Write the Parent info:
		DELETE FROM @ExecResult

		INSERT INTO @ExecResult(RequestorEntityID,DataTableType,DataID,JSONdata)
		EXEC MBOA.RafikiDB.dbo.WriteDataToDB 0,@WriteType,@JSONvalue,@ToUpdateEntityID

		SELECT @CheckJSONdata = JSONdata
		FROM @ExecResult
		-- There is an error - print it
		IF CHARINDEX('"DataError"',@CheckJSONdata) <> 0
			PRINT @CheckJSONdata
		ELSE
			PRINT CAST(@ToUpdateEntityID AS NVARCHAR) + ' - GOOD ' + @CheckJSONdata
			
		FETCH NEXT FROM @WriteCursor
		INTO @JSONvalue,@ToUpdateEntityID,@JSONvalue2,@WriteType
	END
	CLOSE @WriteCursor
	DEALLOCATE @WriteCursor

	DELETE FROM @ExecResult
	DELETE FROM @JSONtoWrite
		
	END
	

	
	PRINT 'Finished Updating Student/Staff/Parent Info'
	
END